package com.jxc.service.impl;

import java.io.UnsupportedEncodingException;
import java.lang.reflect.Array;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jxc.dao.BaseDaoI;
import com.jxc.model.po.Brand;
import com.jxc.model.po.Damagelist;
import com.jxc.model.po.DamagelistItem;
import com.jxc.model.po.DeliverlistItem;
import com.jxc.model.po.Discount;
import com.jxc.model.po.Goods;
import com.jxc.model.po.GoodsRepertory;
import com.jxc.model.po.GoodsType;
import com.jxc.model.po.OrderReturnlistItem;
import com.jxc.model.po.OrderlistItem;
import com.jxc.model.po.Repertory;
import com.jxc.model.po.SaleReturnlistItem;
import com.jxc.model.po.SalelistItem;
import com.jxc.model.po.Transferlist;
import com.jxc.model.po.TransferlistItem;
import com.jxc.model.vo.DataGrid;
import com.jxc.model.vo.VGoods;
import com.jxc.model.vo.VGoodsRepertory;
import com.jxc.model.vo.VOrderlistItem;
import com.jxc.model.vo.VSalelist;
import com.jxc.model.vo.VSalelistItem;
import com.jxc.model.vo.VUser;
import com.jxc.service.GoodsServiceI;
import com.jxc.service.OrderServiceI;
import com.jxc.service.SalelistItemServiceI;

@Service("goodsService")
public class GoodsServiceImpl implements GoodsServiceI {

	@Autowired
	private BaseDaoI<Goods> goodsDao;
	@Autowired
	private BaseDaoI<OrderlistItem> orderDao;
	@Autowired
	private BaseDaoI<GoodsRepertory> goodsrepertoryDao;
	@Autowired
	private BaseDaoI<TransferlistItem> itemsDao;
	@Autowired
	private BaseDaoI<DeliverlistItem> DeliverlistitemsDao;
	@Autowired
	private BaseDaoI<GoodsRepertory> goodsreDao;
	@Autowired
	private OrderServiceI orderServiceI;
	@Override
	public List<VGoods> combobox(VGoods vgoods) {
		List<VGoods> vgoodsz = new ArrayList<VGoods>();
		vgoodsz.addAll(this.changeModel(this.find(vgoods)));
		return vgoodsz;
	}

	@Override
	public DataGrid datagrid(VGoods vgoods) {
		DataGrid dg = new DataGrid();
		dg.setRows(this.changeModel(this.find(vgoods)));
		dg.setTotal(this.total(vgoods));
		return dg;
	}

	@Override
	public DataGrid unsaleDatagrid(VGoods vgoods) {
		DataGrid dg = new DataGrid();
		dg.setRows(this.changeModel(this.findunsalableGoods(vgoods)));
		dg.setTotal(this.total_unsale(vgoods));
		return dg;
	}

	@Override
	public void delete(String ids) {
		if (ids != null) {
			for (String id : ids.split(",")) {
				if (!id.trim().equals("0")) {
					Goods goods = goodsDao.get(Goods.class, Long.parseLong(id.trim()));
					if (goods != null) {
						goods.setIsDelete(true);
						goodsDao.update(goods);
					}
				}
			}
		}
	}

	@Override
	public void updateLastSaleDate(String ids) {
		if (ids != null) {
			for (String id : ids.split(",")) {
				if (!id.trim().equals("0")) {
					Goods goods = goodsDao.get(Goods.class, Long.parseLong(id.trim()));
					if (goods != null) {
						goods.setLastedSaleTime(new Date());
						goodsDao.update(goods);
					}
				}
			}
		}
	}

	@Override
	public void save(VGoods vgoods) {
		Goods goods = new Goods();
		BeanUtils.copyProperties(vgoods, goods);
		
		if (vgoods.getBid() != null) {
			goods.setBrand(new Brand(vgoods.getBid()));
		}
		if (vgoods.getDid() != null) {
			goods.setDiscount(new Discount(vgoods.getDid()));
		}
		if (vgoods.getGtid() != null) {
			goods.setGoodsType(new GoodsType(vgoods.getGtid()));
		}
		this.goodsDao.saveOrUpdate(goods);
	}

	@Override
	public void update(VGoods vgoods) {
		Goods goods = goodsDao.get(Goods.class, vgoods.getId());
		BeanUtils.copyProperties(vgoods, goods, new String[] { "id", "isDelete","lastedSaleTime","initAmount","orderTotal","orderReturnTotal","saleTotal","saleReturnTotal","damageTotal","transferTotal","repertoryTotal" });
		if (vgoods.getBid() != null) {
			goods.setBrand(new Brand(vgoods.getBid()));
		}
		if (vgoods.getDid() != null) {
			goods.setDiscount(new Discount(vgoods.getDid()));
		}
		if (vgoods.getGtid() != null) {
			goods.setGoodsType(new GoodsType(vgoods.getGtid()));
		}
		this.goodsDao.update(goods);
	}

	private List<VGoods> changeModel(List<Goods> goodsz) {
		List<VGoods> vgoodsz = new ArrayList<VGoods>();
		if (goodsz != null && goodsz.size() > 0) {
			for (Goods goods : goodsz) {
				VGoods vgoods = new VGoods();
				
				BeanUtils.copyProperties(goods, vgoods);
//				if (goods.getRepertory() != null) {
//					Repertory repertory = goods.getRepertory();
//					vgoods.setRid(repertory.getId());
//					vgoods.setRname(repertory.getName());
//				}
				if (goods.getBrand() != null) {
					if(goods.getBrand().getDiscount()!=null){
						Discount da = goods.getBrand().getDiscount();
						vgoods.setDid(da.getId());
						vgoods.setDiscountNum(da.getDiscountNum());
						Double total = 0d;
						DecimalFormat df = new DecimalFormat("#.0");
						total =  Double.parseDouble(df.format(vgoods.getDiscountNum()*vgoods.getShowPrice()*0.1));
						vgoods.setSaleprice1(total);
					}
					Brand brand = goods.getBrand();
					vgoods.setBid(brand.getId());
					vgoods.setBname(brand.getName());
					
				}
				if (goods.getGoodsType() != null) {
					GoodsType goodsType = goods.getGoodsType();
					vgoods.setGtid(goodsType.getId());
					vgoods.setGtname(goodsType.getName());
				}
				
//				if (goods.getDiscount() != null) {
//					Discount discount = goods.getDiscount();
//					vgoods.setDid(discount.getId());
//					vgoods.setDiscountNum(discount.getDiscountNum());
//				}
				vgoodsz.add(vgoods);
			}
		}
		return vgoodsz;
	}

	private List<Goods> find(VGoods vgoods) {
		try {
			String hql = "select t from Goods t join t.brand b join  b.repertoryBrandList gr where 1=1  ";

			List<Object> values = new ArrayList<Object>();
			hql = addWhere(vgoods, hql, values);

			if (vgoods.getSort() != null && vgoods.getOrder() != null) {
				hql += " order by t." + vgoods.getSort() + " " + vgoods.getOrder();
			}
			return goodsDao.find(hql, values, vgoods.getPage(), vgoods.getRows());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private List<Goods> findunsalableGoods(VGoods vgoods) {
		try {
			String hql = " select t from Goods t join t.brand b join  b.repertoryBrandList gr where 1=1  and t.lastedSaleTime <= ? ";

			List<Object> values = new ArrayList<Object>();
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.MONTH, -3);
			values.add(cal.getTime());
			hql = addWhere(vgoods, hql, values);

			if (vgoods.getSort() != null && vgoods.getOrder() != null) {
				hql += " order by t." + vgoods.getSort() + " " + vgoods.getOrder();
			}
			return goodsDao.find(hql, values, vgoods.getPage(), vgoods.getRows());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	private Long total(VGoods vgoods) {
		String hql = " select count(*) from (" +
				" select count(*) from spgl_goods t inner join rkgl_repertory_brand gr inner join " +
				" spgl_brand b  inner join rkgl_repertory rk inner join spgl_goods_type gt  where 1=1  and gr.BRAND_ID=b.ID and " +
				" t.is_Delete = 0  and   t.brand_id=b.id and  rk.id=gr.repertory_id ";
				if (vgoods.getId() != null) {
					hql += " and t.id ="+vgoods.getId();
				}
				if (vgoods.getBarcode() != null && !"".equals(vgoods.getBarcode().trim())) {
					hql += " and t.barcode like '%" + vgoods.getBarcode() + "%'";
				}if (vgoods.getName() != null && !"".equals(vgoods.getName().trim())) {
					hql += " and t.name like  '%" + vgoods.getBarcode() + "%'";
				}
				if (vgoods.getType() != null && !"".equals(vgoods.getType().trim())) {
					hql += " and t.type like  '%" + vgoods.getType() + "%'";
				}
				if (vgoods.getColor() != null && !"".equals(vgoods.getColor().trim())) {
					hql += " and t.color like  '%" + vgoods.getColor() + "%'";
				}
				if (vgoods.getSize() != null && !"".equals(vgoods.getSize().trim())) {
					hql += " and t.size like  '%" + vgoods.getSize() + "%'";
				}
				if (vgoods.getGtid() != null) {
					hql += " and  gt.id  = "+vgoods.getGtid();
				}
				if (vgoods.getBid() != null) {
					hql += " and t.brand_id  = "+vgoods.getBid();
				}
				if (vgoods.getRid() != null) {
					hql += " and gr.repertory_id = "+vgoods.getRid();
				}
				if (vgoods.getUid() != null) {
					hql += " and b.user_id =  "+vgoods.getUid();
				}
			hql += " group  by t.id order by t.name desc )aa";
   
			BigInteger bi = goodsDao.countSQL(hql);
		return bi.longValue();
	}

	private Long total_unsale(VGoods vgoods) {
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.MONTH, -3);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dat = sdf.format(cal.getTime());
		String hql = " select count(*) from (" +
				" select count(*) from spgl_goods t inner join rkgl_repertory_brand gr inner join " +
				" spgl_brand b  inner join rkgl_repertory rk  where 1=1  and gr.BRAND_ID=b.ID and " +
				"  t.is_Delete = 0 and t.lasted_Sale_Time <="+dat;
				if (vgoods.getId() != null) {
					hql += " and t.id ="+vgoods.getId();
				}
				if (vgoods.getBarcode() != null && !"".equals(vgoods.getBarcode().trim())) {
					hql += " and t.barcode like '%" + vgoods.getBarcode() + "%'";
				}if (vgoods.getName() != null && !"".equals(vgoods.getName().trim())) {
					hql += " and t.name like  '%" + vgoods.getBarcode() + "%'";
				}
				if (vgoods.getType() != null && !"".equals(vgoods.getType().trim())) {
					hql += " and t.type like  '%" + vgoods.getType() + "%'";
				}
				if (vgoods.getColor() != null && !"".equals(vgoods.getColor().trim())) {
					hql += " and t.color like  '%" + vgoods.getColor() + "%'";
				}
				if (vgoods.getSize() != null && !"".equals(vgoods.getSize().trim())) {
					hql += " and t.size like  '%" + vgoods.getSize() + "%'";
				}
				if (vgoods.getGtid() != null) {
					hql += " and  gt.id  = "+vgoods.getGtid();
				}
				if (vgoods.getBid() != null) {
					hql += " and t.brand_id  = "+vgoods.getBid();
				}
				if (vgoods.getRid() != null) {
					hql += " and gr.repertory_id = "+vgoods.getRid();
				}
				if (vgoods.getUid() != null) {
					hql += " and b.user_id =  "+vgoods.getUid();
				}
			hql += " group  by t.id order by t.name desc )aa";
   
			BigInteger bi = goodsDao.countSQL(hql);
		return bi.longValue();
	}

	
	private String addWhere(VGoods vgoods, String hql, List<Object> values) {
		if (vgoods.getId() != null) {
			hql += " and t.id = ? ";
			values.add(vgoods.getId());
		}
		if (vgoods.getBarcode() != null && !"".equals(vgoods.getBarcode().trim())) {
			hql += " and t.barcode like ? ";
			values.add("%%" + vgoods.getBarcode() + "%%");
		}if (vgoods.getName() != null && !"".equals(vgoods.getName().trim())) {
			hql += " and t.name like ? ";
			values.add("%%" + vgoods.getName() + "%%");
		}
		if (vgoods.getType() != null && !"".equals(vgoods.getType().trim())) {
			hql += " and t.type like ? ";
			values.add("%%" + vgoods.getType() + "%%");
		}
		if (vgoods.getColor() != null && !"".equals(vgoods.getColor().trim())) {
			hql += " and t.color like ? ";
			values.add("%%" + vgoods.getColor() + "%%");
		}
		if (vgoods.getSize() != null && !"".equals(vgoods.getSize().trim())) {
			hql += " and t.size like ? ";
			values.add("%%" + vgoods.getSize() + "%%");
		}
		if (vgoods.getGtid() != null) {
			hql += " and t.goodsType.id  = ? ";
			values.add(vgoods.getGtid());
		}
		if (vgoods.getBid() != null) {
			hql += " and t.brand.id  = ? ";
			values.add(vgoods.getBid());
		}
		if (vgoods.getRid() != null) {
			hql += " and gr.repertory.id = ? ";
			values.add(vgoods.getRid());
		}
		if (vgoods.getUid() != null) {
			hql += " and t.brand.user.id = ? ";
			values.add(vgoods.getUid());
		}
		hql += " and t.isDelete = 0 group by t.id";
		return hql;
	}
	
	private String addWhereGoodsRepertory(VGoods vg, String hql, List<Object> values) {
		try{
			if(vg.getRid()!=null){
				hql += " and r.repertory.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and re.user.id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				try {
					String ba = new String(vg.getBarcode().trim().getBytes("ISO-8859-1"),"utf-8");
					hql += " and t.barcode like "+"'%%"+ba+"%%'";
				} catch (UnsupportedEncodingException e) {
					e.printStackTrace();
				}
			}if (vg.getType() != null && !"".equals(vg.getType().trim().trim())) {
				String ba = new String(vg.getType().getBytes("ISO-8859-1"),"utf-8");
				System.out.println(ba);
				hql += " and t.type like "+"'%%"+ba+"%%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = new String(vg.getColor().trim().getBytes("ISO-8859-1"),"utf-8");
				hql += " and t.color like "+"'%%"+ba+"%%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize().trim()+ "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goodsType.id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand.id  ="+vg.getBid();
			}
			hql += " and t.isDelete = 0 ";
		}catch(Exception e){
			e.printStackTrace();
		}
		
		System.out.println(vg.getRows());
		return hql;
	}

	@Override
	public List<VGoods> search(VGoods vgoods) {
		String hql = "";
		List<Object> values = new ArrayList<Object>();
		if(vgoods.getRid()!=null){
			hql = "select t from Goods t join t.brand b join b.repertoryBrandList rb join rb.repertory r  where t.isDelete=false and t.barcode like ? and r.id=? ";
			String bar = vgoods.getBarcode().trim();
			try {
				String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
				values.add("%%"+newStr+"%%");
				values.add(vgoods.getRid());
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}  
		}else{
			hql = "select t from Goods t join t.brand b join b.user u where t.isDelete=false and u.id=? ";
			if(vgoods.getType()!=null&&!vgoods.getType().equals("")){
				hql+=" and t.type like '%"+vgoods.getType()+"%' ";
			}
			
			try {
				if(vgoods.getBarcode()!=null&&!vgoods.getBarcode().equals("")){
					
					String bar = vgoods.getBarcode().trim();
					String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
						hql+=" and t.barcode like '%"+bar+"%'";
				}
				
				values.add(vgoods.getUid());
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}  
		}
		List<Goods> list = goodsDao.find(hql, values);
		List<VGoods> result = changeModel(list);
		return result;
	}
	

	@Override
	public List<VGoods> searchNoLike(VGoods vgoods) {
		String hql = "";
		List<Object> values = new ArrayList<Object>();
		if(vgoods.getRid()!=null){
			hql = "select t from Goods t join t.brand b join b.repertoryBrandList rb join rb.repertory r  where t.isDelete=false and t.barcode like ? and r.id=? ";
			String bar = vgoods.getBarcode().trim();
			try {
				String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
				values.add(newStr);
				values.add(vgoods.getRid());
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}  
		}else{
			hql = "select t from Goods t join t.brand b join b.user u where t.isDelete=false and t.barcode like ? and u.id=? ";
			String bar = vgoods.getBarcode().trim();
			try {
				String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
				values.add(newStr);
				values.add(vgoods.getUid());
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}  
		}
		List<Goods> list = goodsDao.find(hql, values);
		List<VGoods> result = changeModel(list);
		return result;
	}
	
	

	public List<VGoods> searchReturn(VGoods vGoods ){
		List<Object> values = new ArrayList<Object>();
		String hql = "";
		String bar = vGoods.getBarcode().trim();
		try {
			String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
			if(vGoods.getUid()!=null){
				hql = "select g.id,g.barcode,g.name,g.show_price,g.type,g.color,g.size,b.name as bname,gt.name as gtname,g.unit,t.num" +
					 " from rkgl_goods_repertory t,spgl_goods g,spgl_brand b,spgl_goods_type gt ,rkgl_repertory r " +
					 " where t.GOODS_ID=g.id and r.ID=t.repertory_id and g.brand_id=b.id and g.goods_type_id=gt.id" +
					 " and  r.IS_DELETE=false " +
					 " and g.is_delete=false and r.user_id="+vGoods.getUid()+" " +
					 		" and r.id="+vGoods.getRid();
				
				if(bar!=null&&!bar.equals("")){
				
					hql+=" and g.barcode like '%"+newStr+"%' ";
				}
				
				if(vGoods.getType()!=null&&!vGoods.getType().equals("")){
					
					hql+=" and g.type like '%"+vGoods.getType()+"%' ";
					
				}
				
				
			}else{
				hql =  "select g.id,g.barcode,g.name,g.show_price,g.type,g.color,g.size,b.name as bname,gt.name as gtname,g.unit,t.num" +
				 " from rkgl_goods_repertory t,spgl_goods g,spgl_brand b,spgl_goods_type gt ,rkgl_repertory r " +
				 " where t.GOODS_ID=g.id and r.ID=t.repertory_id and g.brand_id=b.id and g.goods_type_id=gt.id" +
				 " and  r.IS_DELETE=false " +
				 " and g.is_delete=false and g.barcode like '%"+newStr+"%' and r.id="+vGoods.getRid();
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		List<Object[]> list = goodsreDao.findBySql(hql);
		List<VGoods> result = checkModelSearchReturn(list);
		return result;
	}
	public List<VGoods> checkModelSearchReturn(List<Object[]> ob){
		List<VGoods> vg =  new ArrayList<VGoods>();
		try {
			for(Object[] o:ob){
				for (int i = 0; i < o.length; i++) {
					VGoods vgoods = new VGoods();
					vgoods.setId(Long.valueOf(o[i].toString()));
					vgoods.setBarcode(o[++i].toString());
					vgoods.setName(o[++i].toString());
					vgoods.setShowPrice(Double.parseDouble(o[++i].toString()));
					vgoods.setType(o[++i].toString());
					vgoods.setColor(o[++i].toString());
					vgoods.setSize(o[++i].toString());
					vgoods.setBname(o[++i].toString());
					vgoods.setGtname(o[++i].toString());
					vgoods.setUnit(o[++i].toString());
					
					vgoods.setAmount(Integer.parseInt(o[++i].toString()));
					vg.add(vgoods);
				} 
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return vg;
	}
			private List<VGoods> changeModelGoodsRe(List<GoodsRepertory> list) {		List<VGoods> vg =  new ArrayList<VGoods>();
		//OrderlistItem or = new OrderlistItem();
		for(GoodsRepertory or: list){
			//OrderlistItem ori = new OrderlistItem();
			VGoods cGoods = new VGoods();
			//or.getGoods().setBrand(or.getOrderlist().get)
//			System.out.println(or.getGoods().getBrand().getName());
			
			BeanUtils.copyProperties(or.getGoods(), cGoods);
			if(or.getRepertory()!=null){
				cGoods.setRid(or.getRepertory().getId());
				cGoods.setRname(or.getRepertory().getName());
			}
			if(or.getGoods().getBrand()!=null){
				cGoods.setBid(or.getGoods().getBrand().getId());
				cGoods.setBname(or.getGoods().getBrand().getName());
			}
			if(or.getGoods().getGoodsType()!=null){
				cGoods.setGtid(or.getGoods().getGoodsType().getId());
				cGoods.setGtname(or.getGoods().getGoodsType().getName());
			}
			if(or.getNum()!=null){
				cGoods.setAmount(or.getNum());
			}
			vg.add(cGoods);
		}
		return vg;
		//return null;
	}

	@Override
	public Long count(VGoods vgoods) {
		return this.total(vgoods);
	}
	
	/**
	 * 获取库存datagrid对象
	 * @param vg
	 * @return DataGrid
	 */
	@Override
	public DataGrid datagridGoodsRepertory(VGoods vg){
		DataGrid dg = new DataGrid();
		dg.setRows(this.findAmounts(vg));
		dg.setTotal(this.totalGoodsRepertory(vg));
		return dg;
	}
	
	private Long totalGoodsRepertory(VGoods vg) {
		String hql = "select count(*) from GoodsRepertory  as r join r.repertory re join r.goods t where t.isDelete=0 ";

		try {
//			List<Object> values = new ArrayList<Object>();
//			if(vg.getRid()!=null){
			
			if(vg.getRid()!=null){
				hql += " and r.repertory.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and re.user.id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				String ba = vg.getBarcode().trim();
				hql += " and t.barcode like "+"'%%"+ba+"%%'";
			}if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = vg.getType().trim();
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = vg.getColor().trim();
				hql += " and t.color like "+"'%"+ba+"%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize().trim()+ "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goodsType.id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand.id  ="+vg.getBid();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return goodsrepertoryDao.count(hql);
	}
	
	public List<VGoods> findAutocomplete(VGoods vg) {
		List<GoodsRepertory> vGoods = new ArrayList<GoodsRepertory>();
		try {
			String hql = "select  r from GoodsRepertory  as r join r.repertory re join r.goods t where t.isDelete=0 ";
			if(vg.getRid()!=null){
				hql += " and r.repertory.id="+vg.getRid();
			}
			if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = vg.getType().trim();
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			vGoods = goodsrepertoryDao.find(hql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return changeModellist(vGoods);
	}
	
	public List<VGoods> findAmounts(VGoods vg) {
		List<GoodsRepertory> vGoods = new ArrayList<GoodsRepertory>();
		try {
			String hql = "select  r from GoodsRepertory  as r join r.repertory re join r.goods t where t.isDelete=0 ";
			if(vg.getRid()!=null){
				hql += " and r.repertory.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and re.user.id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				String ba = vg.getBarcode().trim();
				hql += " and t.barcode like "+"'%%"+ba+"%%'";
			}if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = vg.getType().trim();
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = vg.getColor().trim();
				hql += " and t.color like "+"'%"+ba+"%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize().trim()+ "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goodsType.id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand.id  ="+vg.getBid();
			}
			if (vg.getSort() != null && vg.getOrder() != null) {
				if(vg.getSort().equals("amount")||vg.getSort()=="amount"){
					hql += " order by r.num "+ vg.getOrder();
				}else if(vg.getSort().equals("total")||vg.getSort()=="total"){
					hql += " order by r.num*t.showPrice "+vg.getOrder();
				}else{
					hql += " order by t." + vg.getSort() + " " + vg.getOrder();
				}
			}
			vGoods = goodsrepertoryDao.find(hql,vg.getPage(),vg.getRows());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return changeModellist(vGoods);
	}
	@Override
	public List<VGoods> findAmountsNoLimit(VGoods vg) {
		List<GoodsRepertory> vGoods = new ArrayList<GoodsRepertory>();
		try {
			String hql = "select  r from GoodsRepertory  as r join r.repertory re join r.goods t where t.isDelete=0 ";
			if(vg.getRid()!=null){
				hql += " and r.repertory.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and re.user.id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				try {
					String ba = new String(vg.getBarcode().getBytes("ISO-8859-1"),"utf-8");
					hql += " and t.barcode like "+"'%"+ba+"%'";
				} catch (UnsupportedEncodingException e) {
					e.printStackTrace();
				}
			}if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = new String(vg.getType().getBytes("ISO-8859-1"),"utf-8");
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = new String(vg.getColor().getBytes("ISO-8859-1"),"utf-8");
				hql += " and t.color like "+"'%"+ba+"%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize() + "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goodsType.id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand.id  ="+vg.getBid();
			}
			
			//为了保证查询的销量，每次只返回前面20条符合条件的结果
			
			//hql+=" LIMIT 1,20";
			
			vGoods = goodsrepertoryDao.find(hql,1,20);
//			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return changeModellist(vGoods);
	}
	
	private String addWhereAm(VGoods vg,String hql){
		try {
			if(vg.getRid()!=null){
				hql += " and r.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and r.user.id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				String ba = vg.getBarcode().trim();
				hql += " and t.barcode like "+"'%%"+ba+"%%'";
			}if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = vg.getType().trim();
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = vg.getColor().trim();
				hql += " and t.color like "+"'%"+ba+"%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize().trim()+ "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goodsType.id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand.id  ="+vg.getBid();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return hql;
	}
	
	private String addWhereAmBySql(VGoods vg,String hql){
		try {
			if(vg.getRid()!=null){
				hql += " and r.id="+vg.getRid();
			}if(vg.getUid()!=null){
				hql += " and r.user_id="+vg.getUid();
			}if (vg.getBarcode() != null && !"".equals(vg.getBarcode().trim())) {
				String ba = vg.getBarcode().trim();
				hql += " and t.barcode like "+"'%%"+ba+"%%'";
			}if (vg.getType() != null && !"".equals(vg.getType().trim())) {
				String ba = vg.getType().trim();
				hql += " and t.type like "+"'%"+ba+"%'";
			}
			if (vg.getColor() != null && !"".equals(vg.getColor().trim())) {
				String ba = vg.getColor().trim();
				hql += " and t.color like "+"'%"+ba+"%'";
			}
			if (vg.getSize() != null && !"".equals(vg.getSize().trim())) {
				hql += " and t.size like "+"'%" + vg.getSize().trim()+ "%'";
			}
			if (vg.getGtid() != null) {
				hql += " and t.goods_type_id  ="+vg.getGtid();
			}
			if (vg.getBid() != null) {
				hql += " and t.brand_id  ="+vg.getBid();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return hql;
	}
	private List<VGoods> changeModelgoodsre(List<Object[]> ob){
   List<VGoods> vg =  new ArrayList<VGoods>();
		try {
			for(Object[] o:ob){
				for (int i = 0; i < o.length; i++) {
					VGoods cGoods = new VGoods();
					cGoods.setId(Long.valueOf(o[i].toString()));
					cGoods.setBarcode(o[++i].toString());
					cGoods.setName(o[++i].toString());
					cGoods.setType(o[++i].toString());
					cGoods.setColor(o[++i].toString());
					cGoods.setSize(o[++i].toString());
					cGoods.setRid(Long.valueOf(o[++i].toString()));
					cGoods.setUid(Long.valueOf(o[++i].toString()));
					cGoods.setRname(o[++i].toString());
					cGoods.setAmount(Integer.valueOf(o[++i].toString()));
					cGoods.setInitAmount(new Long(0)); 
					if(cGoods.getRid()!=null){
						String hql = "select sum(t.num) from rkgl_orderlist_item t,rkgl_orderlist o ,rkgl_repertory r where " +
								" t.orderlist_id=o.id and r.id=o.repertory_id and r.is_delete=false  and  o.is_deleted=false " +
								" and r.user_id="+cGoods.getUid()+"  and o.repertory_id="+cGoods.getRid()+
								" and t.goods_id="+cGoods.getId();//进货数量
						BigDecimal xs = goodsDao.countSQL2(hql);
						if(xs==null)xs=new BigDecimal(0);
						Long as = Long.parseLong(xs.toString());
						cGoods.setOrderTotal(as); 
					    String hql4 = "select sum(t.num) from rkgl_orderreturnlist_item t,rkgl_orderreturnlist o ,rkgl_repertory r " +
						" where t.ORDERRETURNLIST_ID=o.ID and r.ID=o.REPERTORY_DI and r.IS_DELETE=false  and  o.ISDELETED=false" +
						" and r.user_id="+cGoods.getUid()+"  and o.REPERTORY_DI="+cGoods.getRid()+
						" and t.goods_id="+cGoods.getId();//进货退货
						BigDecimal th = goodsDao.countSQL2(hql4);
						if(th==null)th=new BigDecimal(0);
						Long tha = Long.parseLong(th.toString()); 
					    cGoods.setOrderReturnTotal(tha);
					    String hql5 = " select sum(t.number) from xsgl_salelist_item t,xsgl_salelist o ,rkgl_repertory r " +
						   " where t.salelist_id=o.id and r.id=o.repertory_id and r.is_delete=false " +
						   " and r.user_id="+cGoods.getUid()+"  and o.repertory_id="+cGoods.getRid()+
						   " and t.goods_id="+cGoods.getId();   //销售 
					    BigDecimal xss = goodsDao.countSQL2(hql5);
					    if(xss==null)xss=new BigDecimal(0);
					    cGoods.setSaleTotal(Long.parseLong(xss.toString()));
					    String hql6 = "select sum(t.num) from xsgl_salereturnlist_item t,xsgl_salereturnlist o ,rkgl_repertory r " +
					    		" where t.salereturnlist_id=o.id and r.id=o.repertory_id and r.is_delete=false" +
					    		" and r.user_id="+cGoods.getUid()+"  and o.repertory_id="+cGoods.getRid()+
								" and t.goods_id="+cGoods.getId(); //销售退货 
					    BigDecimal xst = goodsDao.countSQL2(hql6);
					    if(xst==null)xst=new BigDecimal(0);
					    cGoods.setSaleReturnTotal(Long.parseLong(xst.toString()));
					    String hql3 = "select sum(t.num) from kcgl_damagelist_item t,kcgl_damagelist o ,rkgl_repertory r " +
					    		" where t.damagelist_id=o.id and r.id=o.repertory_id and r.is_delete=false " + 
					    		" and r.user_id="+cGoods.getUid()+"  and o.repertory_id="+cGoods.getRid()+
								" and t.goods_id="+cGoods.getId();  //报损
					    BigDecimal bs = goodsDao.countSQL2(hql3);
					    if(bs==null)bs=new BigDecimal(0);
					    cGoods.setDamageTotal(Long.parseLong(bs.toString()));
//					    String hql2 = "select sum(t.num) from TransferlistItem t join t.transferlist i join i.repertory r where r.isDelete=false " +
//			    		"and r.user.id="+or.getRepertory().getUser().getId()+" and r.id="+or.getRepertory().getId()+
//			    		" and t.goods.id="+or.getGoods().getId(); //调拨
//					    Long db = goodsDao.count(hql2);
//					    if(db==null)db=new Long(0);
//					    cGoods.setTransferTotal(db);
						String hql7 ="select sum(t.num) from rkgl_goods_repertory t ,rkgl_repertory r,spgl_goods g"+
									" where t.goods_id=g.id and r.id=t.repertory_id and r.is_delete=false and g.is_delete=false "+
									" and r.user_id="+cGoods.getUid()+"  and t.repertory_id="+cGoods.getRid()+
									" and t.goods_id="+cGoods.getId(); //库存
						BigDecimal kc = goodsDao.countSQL2(hql7);
						if(kc==null)kc=new BigDecimal(0);
						cGoods.setRepertoryTotal(Long.parseLong(kc.toString()));
						vg.add(cGoods);
				   }
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return vg;
	}

	private List<VGoods> changeModellist(List<GoodsRepertory> vGoods) {
		List<VGoods> vg =  new ArrayList<VGoods>();
		
		try {
			for(GoodsRepertory or: vGoods){
				VGoods cGoods = new VGoods();
				if(or.getGoods()!=null){
				BeanUtils.copyProperties(or.getGoods(), cGoods);
				}
				if(or.getRepertory()!=null){
					cGoods.setRid(or.getRepertory().getId());
					cGoods.setRname(or.getRepertory().getName());
					if(or.getRepertory().getUser()!=null){
						if(or.getRepertory().getUser().getUser()!=null){
							if(or.getRepertory().getUser().getUser().getUserType()!=null){
								cGoods.setUpusertype(or.getRepertory().getUser().getUser().getUserType());
							}
						}
					}
				}if(or.getGoods().getBrand()!=null){
					cGoods.setBid(or.getGoods().getBrand().getId());
					cGoods.setBname(or.getGoods().getBrand().getName());
					if(or.getGoods().getBrand().getDiscount()!=null){
						cGoods.setDiscountNum(or.getGoods().getBrand().getDiscount().getDiscountNum());
						DecimalFormat df = new DecimalFormat("#.00");  
						if(cGoods.getSalePrice()==null){
							cGoods.setSalePrice(Double.parseDouble(df.format(cGoods.getShowPrice()*cGoods.getDiscountNum()*0.1)));
							System.out.println("cGoods.getShowPrice():"+cGoods.getShowPrice()+"cGoods.getDiscountNum():"+cGoods.getDiscountNum()+"all:"+Double.parseDouble(df.format(cGoods.getShowPrice()*cGoods.getDiscountNum()*0.1)));
						}
					}else{
						if(cGoods.getSalePrice()==null){
							cGoods.setSalePrice(cGoods.getShowPrice());
						}
					}
				}if(or.getGoods().getGoodsType()!=null){
					cGoods.setGtid(or.getGoods().getGoodsType().getId());
					cGoods.setGtname(or.getGoods().getGoodsType().getName());
				}if(or.getNum()!=null){
					cGoods.setAmount(or.getNum());
				}
				vg.add(cGoods);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return vg;
	}

	@Override
	public List<VGoods> findByOrid(VGoods vg) {
		List<Object[]> vGoods = new ArrayList<Object[]>();
		try {
			if(vg.getRid()!=null&&vg.getUid()!=null){
				String hql = "select  g.id,g.barcode,g.name,g.type,g.color,g.size,t.repertory_id,r.user_id,r.name as rname,t.num from rkgl_goods_repertory t ,rkgl_repertory r,spgl_goods g" +
						"  where t.GOODS_ID=g.id and r.ID=t.repertory_id and r.IS_DELETE=false and g.is_delete=false";
				hql += " and r.id="+vg.getRid();
				hql += " and r.user_id="+vg.getUid();
				hql += " group by t.id";
				vGoods = goodsrepertoryDao.findBySql(hql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return changeModelgoodsre(vGoods);
	}

	@Override
	public List<VGoods> findGoods(String ids) {
		List<TransferlistItem> itemslist = new ArrayList<TransferlistItem>();
		for(String id:ids.trim().split(",")){
			TransferlistItem i = new TransferlistItem();
			String hql = "select i from  Transferlist t join t.items i join i.goods where i.id="+new Long(id);
			i = itemsDao.find(hql).get(0);
			itemslist.add(i);
		}
		return changeModelItems(itemslist);
		//return null;
	}
	
	private List<VGoods> changeModelItems(List<TransferlistItem> items){
		List<VGoods> vg = new ArrayList<VGoods>();
		for(TransferlistItem tr: items){
			if(tr.getGoods()!=null){
				VGoods cg = new VGoods();
				BeanUtils.copyProperties(tr.getGoods(), cg);
				if(tr.getGoods().getBrand()!=null){
					cg.setBid(tr.getGoods().getBrand().getId());
					cg.setBname(tr.getGoods().getBrand().getName());
				}if(tr.getGoods().getGoodsType()!=null){
					cg.setGtid(tr.getGoods().getGoodsType().getId());
					cg.setGtname(tr.getGoods().getGoodsType().getName());
				}if(tr.getId()!=null){
					cg.setItemsid(tr.getId());
				}if(tr.getNum()!=null){
					cg.setNum(new Long(tr.getNum()));
				}
				vg.add(cg);
			}
			
		}
		return vg;
	}
	
	
	public VGoods  findTotal(VGoods vg){
		String hql = "select sum(d.num) from GoodsRepertory d join d.repertory r join d.goods t " +
				" where r.isDelete=false and t.isDelete=false ";
		hql = addWhereAm(vg, hql);
	    Long  zkc  =  goodsDao.count(hql);
	    if(zkc==null)zkc=new Long(0);
	    VGoods vGoods = new VGoods();
	    vGoods.setZkc(zkc);
	    String hql2 = "select sum(d.num) from OrderlistItem d join d.orderlist o join o.repertory r join d.goods t " +
	    		"where r.isDelete=false and t.isDelete=false and  o.isDeleted=false ";
	    System.out.println(vg.getRid());
	    hql2 = addWhereAm(vg, hql2);
	    Long zrks = goodsDao.count(hql2);
	    if(zrks==null)zrks=new Long(0);
	    vGoods.setZrks(zrks); 
	    //报损数量
	    String hql3 = "select sum(d.num) from DamagelistItem d join d.damagelist i join i.repertory r join d.goods t " +
	    		"where r.isDelete=false and t.isDelete=false ";
	    hql3 = addWhereAm(vg, hql3);
	    Long bs = goodsDao.count(hql3);
	    //退货 
	    String hql4 = "select sum(d.num) from OrderReturnlistItem d join d.orderReturnlist o join o.repertory r join d.goods t " +
	    		" where r.isDelete=false and o.isDeleted=false and  t.isDelete=false  ";
	    hql4 = addWhereAm(vg, hql4);
	    Long th = goodsDao.count(hql4);
 		//销售
	    String hql5 = "select sum(d.number) from SalelistItem d join d.salelist s join s.repertory r join d.goods t where" +
	    		" r.isDelete=false  and  t.isDelete=false ";
	    hql5 = addWhereAm(vg, hql5);
	    Long xs = goodsDao.count(hql5);
	    if(bs==null)
	    	bs=new Long(0);
	    if(th==null)
	    	th=new Long(0);
	    if(xs==null)
	    	xs=new Long(0);
	    Long sum = bs+th+xs;
	    vGoods.setZcks(sum);
	    //库存总金额
	    String hql6 = "select sum(d.num*t.salePrice) from GoodsRepertory d join d.goods t join t.brand b join d.repertory r where " +
	    		" t.isDelete=false and b.isDelete=false and  r.isDelete=false ";
	    hql6 = addWhereAm(vg, hql6);
	    Double kczje = goodsDao.count1(hql6);
	    if(kczje==null)kczje=0d;
	    vGoods.setKczje(kczje);
	    //吊牌价总金额
	    String hql8 = "select sum(d.num*t.showPrice) from GoodsRepertory d join d.goods t join t.brand b join d.repertory r where " +
	    		" t.isDelete=false and b.isDelete=false and  r.isDelete=false ";
	    hql8 = addWhereAm(vg, hql8);
	    Double dpzje = goodsDao.count1(hql8);
	    if(dpzje==null)dpzje=0d;
	    Double dp = Double.parseDouble(new java.text.DecimalFormat("#.00").format(dpzje));
	    if(dp==null)dp=0d;
	    vGoods.setDpzje(dp);
	    GoodsRepertory gr = null ;

	    Double cc = findKCBySql(vg);
	    //成本总金额
	 	if(cc==null)cc=0d;
	    vGoods.setCbzje(cc); 
	    return vGoods;
	}
	
/*	 public Double findKC(VGoods vg ){ 
		 //成本总金额
		int kc = 0;
		Double cb = 0d;
	    String hql = "select d from GoodsRepertory d join d.goods t join t.brand b join d.repertory r where " +
		" t.isDelete=false and b.isDelete=false and  r.isDelete=false ";
	    hql = addWhereAm(vg, hql);
	 	List<GoodsRepertory>  grs =  goodsrepertoryDao.find(hql);
	 	GoodsRepertory gr =  null;
	 	for(int i=0;i<grs.size();i++){
	 		gr = grs.get(i);
	 		List<VOrderlistItem> vsi = orderServiceI.findByGoods(gr,"desc");
	 		for(int j=0;j<vsi.size();){
	 			VOrderlistItem voli = vsi.get(j);
	 			if(kc<=0){
	 				kc = gr.getNum();
	 			}
	 			if(kc-voli.getNum()>0){
	 				cb += voli.getNum()*voli.getBuyPrice();
	 				kc = gr.getNum()-voli.getNum();
	 				j++;
	 			}else{
	 				cb += kc*voli.getBuyPrice();
	 				kc = 0;
	 				break;
	 			}
	 		}
	 	}
		return cb; 
	}*/

	 public Double findKCBySql(VGoods vg ){ 
		 //成本总金额
		int kc = 0;
		Double cb = 0d;
	    String hql = " select d.id,d.num,d.Goods_id,d.repertory_id from rkgl_goods_repertory d ,spgl_goods t ,rkgl_repertory r where " +
	    		" t.is_delete=false and  r.is_delete=false and d.goods_id=t.id " +
	    		" and d.repertory_id=r.id ";
	    hql = addWhereAmBySql(vg, hql);
	 	List<Object[]>   grs =  goodsrepertoryDao.findBySql(hql);
	  
	 	for(Object[] o:grs){
	 		for (int j = 0; j < o.length;j++) {
	 			VGoodsRepertory vr = new VGoodsRepertory();
				vr.setId(Long.parseLong(o[j].toString()));//主键
				vr.setNum(Integer.parseInt(o[++j].toString()));//库存
				vr.setGid(Long.parseLong(o[++j].toString()));//商品ID
				vr.setRid(Long.parseLong(o[++j].toString()));//仓库ID
				List<Object[]> vsi = orderServiceI.findByGoods(vr,"desc");
				if(vsi.size()==0){
					String hs = " select buy_price from spgl_goods where id="+vr.getGid();
					Double bd = goodsDao.countSQL1(hs);
					if(bd==null)bd=0d; 
					cb += vr.getNum()*bd;
				}else{
					List<VOrderlistItem> volis = new ArrayList<VOrderlistItem>();
					for(Object[] vs:vsi){
						for (int i = 0; i < vs.length;i++) {
							VOrderlistItem voli = new VOrderlistItem();
							voli.setNum(Integer.parseInt(vs[i].toString()));
							voli.setBuyPrice(Double.parseDouble(vs[++i].toString()));
							volis.add(voli);
						}
					}
					for (int i = 0; i < volis.size(); ) {
						VOrderlistItem voli = volis.get(i);
			 			if(kc<=0){
			 				kc = vr.getNum();
			 			}
			 			if(kc-voli.getNum()>0){
			 				cb += voli.getNum()*voli.getBuyPrice();
			 				kc = vr.getNum()-voli.getNum();
			 				i++;
			 			}else{
			 				cb += kc*voli.getBuyPrice();
			 				kc = 0;
			 				break;
			 			}
					}
				}
			}
	 	}
		return cb; 
	}
	 
	@Override
	public List<VGoods> findForGoods(VGoods vgoods) {
		String hql = "";
		List<Object> values = new ArrayList<Object>();
		hql = "select t from Goods t  where t.isDelete=false and t.id=? ";
		values.add(vgoods.getRid());
		List<Goods> list = goodsDao.find(hql, values);
		List<VGoods> result = changeModel(list);
		return result;
	}
/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#searchDeliver(com.jxc.model.vo.VGoods)
	 */
	public List<VGoods> searchDeliver(VGoods vGoods ){
		List<Object> values = new ArrayList<Object>();
		String hql = "";
		hql = "select t from Goods t join t.brand b  join b.user u where t.isDelete=false and t.barcode like ? and u.id=?";
		String bar = vGoods.getBarcode().trim();
		try {
			String newStr = new String(bar.getBytes("ISO-8859-1"), "UTF-8");
			values.add("%%"+newStr+"%%");
			values.add(vGoods.getUid());
		} catch (Exception e) {
			e.printStackTrace();
		}  
		List<Goods> list = goodsDao.find(hql, values);
		List<VGoods> result = changeModelGoods(list);
		return result;
	}
	
	private List<VGoods> changeModelGoods(List<Goods> list) {
		List<VGoods> vg =  new ArrayList<VGoods>();
		for(Goods goods: list){
			VGoods cGoods = new VGoods();
			BeanUtils.copyProperties(goods, cGoods);
			vg.add(cGoods);
		}
		return vg;
		//return null;
	}
	
	/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#findGoodsDeliver(java.lang.String)
	 */
	@Override
	public List<VGoods> findGoodsDeliver(String ids) {
		List<DeliverlistItem> itemslist = new ArrayList<DeliverlistItem>();
		for(String id:ids.trim().split(",")){
			DeliverlistItem i = new DeliverlistItem();
			String hql = "select i from  Deliverlist t join t.items i join i.goods where i.id="+new Long(id);
			i = DeliverlistitemsDao.find(hql).get(0);
			itemslist.add(i);
		}
		return changeModelDeliverItems(itemslist);
		//return null;
	}
	private List<VGoods> changeModelDeliverItems(List<DeliverlistItem> items){
		List<VGoods> vg = new ArrayList<VGoods>();
		for(DeliverlistItem dl: items){
			if(dl.getGoods()!=null){
				VGoods cg = new VGoods();
				BeanUtils.copyProperties(dl.getGoods(), cg);
				if(dl.getGoods().getBrand()!=null){
					cg.setBid(dl.getGoods().getBrand().getId());
					cg.setBname(dl.getGoods().getBrand().getName());
				}if(dl.getGoods().getGoodsType()!=null){
					cg.setGtid(dl.getGoods().getGoodsType().getId());
					cg.setGtname(dl.getGoods().getGoodsType().getName());
				}if(dl.getId()!=null){
					cg.setItemsid(dl.getId());
				}if(dl.getNum()!=null){
					cg.setNum(new Long(dl.getNum()));
				}
				vg.add(cg);
			}
			
		}
		return vg;
	}

	/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#matchGoods(java.lang.Long, com.jxc.model.vo.VUser)
	 */
	@Override
	public DataGrid matchGoods(Long id, VUser vuser) {
		// TODO Auto-generated method stub
		DataGrid dg = new DataGrid();
		List<VGoods> result = new ArrayList<VGoods>();
		try{
			Goods goods =  goodsDao.get(Goods.class, id);
			String hql = "select t from Goods t join t.brand b join b.user u where 1=1 ";
			List<Object> param = new ArrayList<Object>();
			
			if(vuser.getId()!=null){
				hql += " and u.id=? ";
				param.add(vuser.getId());
			}
			if(goods.getType()!=null){
				hql += " and t.type=? ";
				param.add(goods.getType());
			}
			
			List<VGoods> vgoodslist = changeModel(goodsDao.find(hql, param));
			dg.setRows(vgoodslist);
			dg.setTotal((long) vgoodslist.size());
		}catch(Exception e){
			e.printStackTrace();
		}		
		return dg;
	}

	/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#datagridPSID(com.jxc.model.vo.VGoods)
	 */
	@Override
	public DataGrid datagridPSID(VGoods model) {
		// TODO Auto-generated method stub
		DataGrid dg = new DataGrid();	
		List<Object[]> vsls = PSIDTotal(model);
		dg.setRows(this.changeModelTotal(vsls,model));
		dg.setTotal((long) vsls.size());
		return dg;
		
	}

	/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#getPSIDTotalChart(java.util.List, com.jxc.model.vo.VGoods)
	 */
	@Override
	public JFreeChart getPSIDTotalChart(List<Object[]> vsls, VGoods model) {
		// TODO Auto-generated method stub
		JFreeChart chart = null;
		try {
			String chartTitle = "进销存日报表";
			String domainLabel ="品牌";
			if(model.getGroupby().trim().equals("brand")){//通过品牌统计
				chartTitle += "(按品牌)";
			}else if(model.getGroupby().trim().equals("color")){//通过颜色统计
				chartTitle += "(按颜色)";
			}else if(model.getGroupby().trim().equals("size")){//通过尺码统计
				chartTitle += "(按尺码)";
			}else if(model.getGroupby().trim().equals("type")){//通过款号统计
				chartTitle += "(按款号)";
			}else if(model.getGroupby().trim().equals("gtype")){//通过商品类型统计
				chartTitle += "(按商品类型)";
			}else{//通过品牌统计
				chartTitle += "(按品牌)";
			}
			
			DefaultCategoryDataset dataset = new DefaultCategoryDataset();

			//  各曲线名称
			String series1 = "进货数量";
			String series2 = "销售数量";
			String series3 = "库存数量";
			String series4 = "进货金额";
			String series5 = "销售金额";
			String series6 = "库存金额";
			String series7 = "进货成本";
			String series8 = "销售成本";
			String series9 = "库存成本";			  
			for(Object[] objarr:vsls){
				/*dataset.addValue(Double.parseDouble(String.valueOf(objarr[1])), (String)objarr[0],series1);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[2])), (String)objarr[0],series2);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[3])), (String)objarr[0],series3);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[4])), (String)objarr[0],series4);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[5])), (String)objarr[0],series5);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[6])), (String)objarr[0],series6);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[7])), (String)objarr[0],series7);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[8])), (String)objarr[0],series8);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[9])), (String)objarr[0],series9);*/
				/*dataset.addValue(Double.parseDouble(String.valueOf(objarr[1])),series1, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[2])),series2, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[3])),series3, (String)objarr[0]);*/
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[4])),series4, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[5])),series5, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[6])),series6, (String)objarr[0]);
			/*	dataset.addValue(Double.parseDouble(String.valueOf(objarr[7])),series7, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[8])),series8, (String)objarr[0]);
				dataset.addValue(Double.parseDouble(String.valueOf(objarr[9])),series9, (String)objarr[0]);*/
			}
			chart = ChartFactory.createLineChart(chartTitle, // chart title
					domainLabel, // domain axis label
				    "金额", // range axis label
				    dataset, // data
				    PlotOrientation.VERTICAL, // orientation
				    true, // include legend
				    true, // tooltips
				    false // urls
				    );
		} catch (Exception e) {
			e.printStackTrace();
		}
		return chart;
		
	}

	/* (non-Javadoc)
	 * @see com.jxc.service.GoodsServiceI#PSIDTotal(com.jxc.model.vo.VGoods)
	 */
	@Override
	public List<Object[]> PSIDTotal(VGoods model) {
		// TODO Auto-generated method stub
		List<Object[]> vsls = new ArrayList<Object[]>();
		/*String sql = " FROM (SELECT  u.user_name username,u.real_name realname,r.name repertoryname,sl.total total,SUM(g.buy_price) buyprice ,sl.total-SUM(g.buy_price) profit " +
				"FROM  `xsgl_salelist_item` sli JOIN `xsgl_salelist` sl JOIN `spgl_goods` g  JOIN `rkgl_repertory` r JOIN `qxgl_user` u " +
				"WHERE sli.salelist_id=sl.id AND sli.goods_id=g.id AND sl.repertory_id=r.id AND sl.user_id=u.id AND r.user_id='"+ model.getUpid() +"' GROUP BY sl.id) t " ;*/
		
		String sql = 
				" SUM(oli.num)-SUM(orli.num) ordernum,SUM(sli.number) salenum,SUM(gr.num) inventory, " +
				" ROUND(SUM(oli.total)-SUM(orli.total),2) ordertotal,  ROUND(SUM(oli.total),2) saletotal,ROUND(SUM(gr.num*g.sale_price),2) inventorytotal, " +
				" ROUND(SUM(g.buy_price*oli.num)-SUM(g.buy_price*orli.num),2) orderbuyprice,ROUND(SUM(g.buy_price*sli.number),2) salebuyprice,ROUND(SUM(gr.num*g.buy_price),2) inventorybuyprice " +
				" FROM `spgl_goods` g JOIN `spgl_goods_type` gt JOIN `spgl_brand` b JOIN `rkgl_orderlist` ol JOIN `rkgl_orderlist_item` oli " +
				"JOIN `rkgl_orderreturnlist` orl JOIN `rkgl_orderreturnlist_item` orli	JOIN `xsgl_salelist` sl " +
				"JOIN `xsgl_salelist_item` sli JOIN `rkgl_repertory` r JOIN `rkgl_goods_repertory` gr " +
				"WHERE g.brand_id=b.id AND  g.goods_type_id=gt.id AND oli.goods_id=g.id AND oli.orderlist_id=ol.id AND orli.goods_id=g.id AND " +
				" orli.orderreturnlist_id=orl.id AND sli.goods_id=g.id AND sli.salelist_id=sl.id AND " +
				" gr.goods_id=g.id AND gr.repertory_id=r.id AND r.user_id="+model.getUpid();
		if(model.getRid()!=null){
			sql+=" AND r.id="+model.getRid();
		}
		
		sql+=" GROUP BY " ;
		
		try{
			if(model.getGroupby().trim().equals("brand")){//通过品牌统计
				sql = " SELECT  b.name bname," + sql + " b.id ORDER BY salenum DESC ";
			}else if(model.getGroupby().trim().equals("color")){//通过颜色统计
				sql = " SELECT  g.color gcolor," + sql + "  g.color ORDER BY salenum DESC ";
			}else if(model.getGroupby().trim().equals("size")){//通过尺码统计
				sql = " SELECT  g.size gsize," + sql + "  g.size ORDER BY salenum DESC ";
			}else if(model.getGroupby().trim().equals("type")){//通过款号统计
				sql = " SELECT  g.type gtype," + sql + "'  g.type ORDER BY salenum DESC ";
			}else if(model.getGroupby().trim().equals("gtype")){//通过商品类型统计
				sql = " SELECT  gt.name gtname," + sql + " gt.name ORDER BY salenum DESC ";
			}else{//通过品牌统计
				sql = " SELECT  b.name btname," + sql + "  b.id ORDER BY salenum DESC ";
			}
			
			sql += " limit 5 ";
			vsls = goodsDao.findBySql(sql);
			
		}catch(Exception e){
			e.printStackTrace();
		}		
		
		return vsls;	
		
	}
	
	/**
	 * 把List<object[]>类型的进销存日报表数据封装成对象用于datagrid显示
	 * @param sls
	 * @param model
	 * @return
	 */
	private List<VGoods> changeModelTotal(List<Object[]> sls,VGoods model) {
		// TODO Auto-generated method stub

	List<VGoods> vsls = new ArrayList<VGoods>();
		
		if (sls != null && sls.size() > 0) {
			for (int i = 0;i < sls.size();i++) {
				VGoods vg = new VGoods();
				if(model.getGroupby().trim().equals("brand")){//通过品牌统计
					vg.setBname((String)(sls.get(i)[0]));
				}else if(model.getGroupby().trim().equals("color")){//通过颜色统计
					vg.setColor((String)(sls.get(i)[0]));
				}else if(model.getGroupby().trim().equals("size")){//通过尺码统计
					vg.setSize((String)(sls.get(i)[0]));
				}else if(model.getGroupby().trim().equals("type")){//通过款号统计
					vg.setType((String)(sls.get(i)[0]));
				}else if(model.getGroupby().trim().equals("gtype")){//通过商品类型统计
					vg.setGtname((String)(sls.get(i)[0]));
				}else{//通过品牌统计
					vg.setBname((String)(sls.get(i)[0]));
				}
				vg.setOrdernum(Long.parseLong(String.valueOf(sls.get(i)[1])));
				vg.setSalenum(Long.parseLong(String.valueOf(sls.get(i)[2])));
				vg.setInventorynum(Long.parseLong(String.valueOf(sls.get(i)[3])));
				
				vg.setOrdertotal(Double.parseDouble(String.valueOf(sls.get(i)[4])));
				vg.setSaletotal(Double.parseDouble(String.valueOf(sls.get(i)[5])));
				vg.setInventorytotal(Double.parseDouble(String.valueOf(sls.get(i)[6])));

				vg.setOrderbuyprice(Double.parseDouble(String.valueOf(sls.get(i)[4])));
				vg.setSalebuyprice(Double.parseDouble(String.valueOf(sls.get(i)[5])));
				vg.setInventorybuyprice(Double.parseDouble(String.valueOf(sls.get(i)[6])));

				vsls.add(vg);
			}
		}
		return vsls;
	}	
}
